# -*- coding: utf-8 -*-
from datetime import timedelta
from time import time
from utils.operators.rich_sql_sensor import RichSqlSensor
# from jms.dm.route.dm_route_whole_shift_base_dt import jms_dm__dm_route_whole_shift_base_dt

label = f"dm_route_whole_shift_base_dt_{int(time())}"
timeout = timedelta(minutes=30).seconds
doris_jms_dm__dm_route_whole_shift_base_dt = RichSqlSensor(
    task_id='doris_jms_dm__dm_route_whole_shift_base_dt',
    pool='broker_load_pool',
    email=['suning@jtexpress.com','yl_bigdata@yl-scm.com'],
    task_concurrency=1,
    conn_id='new_doris',
    pre_sql=f"""
    TRUNCATE TABLE jms_dm.dm_route_whole_shift_base_dt PARTITION (
         p{{{{ execution_date | cst_ds_nodash }}}}
    );
    LOAD LABEL jms_dm.{label} (
        DATA INFILE(
          "hdfs://{{{{ var.value.hadoop_namespace }}}}/dw/hive/jms_dm.db/external/dm_route_whole_shift_base_dt/dt={{{{ execution_date | cst_ds }}}}/*"
        )
        INTO TABLE dm_route_whole_shift_base_dt
        FORMAT AS 'PARQUET'(
            in_from_code
            ,in_from_provider_code
            ,in_from_city_code
            ,in_from_area_code
            ,out_to_provider_code
            ,out_to_city_code
            ,out_to_area_code
            ,out_to_code
            ,in_branch_id
            ,main_id
            ,out_branch_id
            ,in_collect_code
            ,in_to_code
            ,start_center_code
            ,search_type
            ,start_network_code
            ,in_from_regional_code
            ,in_from_regional_desc
            ,in_from_financial_center_code
            ,in_from_financial_center_desc
            ,in_from_provider_desc
            ,in_from_city_desc
            ,in_from_area_desc
            ,in_collect_name
            ,in_from_name
            ,in_to_name
            ,in_edge_latest_warehousing
            ,in_edge_planned_departure
            ,in_edge_planned_arrival
            ,in_edge2_planned_departure
            ,in_edge2_planned_arrival
            ,branch_in_span_days
            ,branch_in_total_time
            ,is_main_route
            ,transfer_type
            ,num_of_transfer
            ,whole_route
            ,e1_center_flow
            ,e1_line_code
            ,e1_planned_departure
            ,e1_runtime
            ,e1_planned_arrival
            ,e1_e2_stop_time
            ,e2_center_flow
            ,e2_line_code
            ,e2_planned_departure
            ,e2_runtime
            ,e2_planned_arrival
            ,e2_e3_stop_time
            ,e3_center_flow
            ,e3_line_code
            ,e3_planned_departure
            ,e3_runtime
            ,e3_planned_arrival
            ,e3_e4_stop_time
            ,e4_center_flow
            ,e4_line_code
            ,e4_planned_departure
            ,e4_runtime
            ,e4_planned_arrival
            ,e4_e5_stop_time
            ,e5_center_flow
            ,e5_line_code
            ,e5_planned_departure
            ,e5_runtime
            ,e5_planned_arrival
            ,e5_e6_stop_time
            ,e6_center_flow
            ,e6_line_code
            ,e6_planned_departure
            ,e6_runtime
            ,e6_planned_arrival
            ,e6_e7_stop_time
            ,e1_end_code
            ,e2_end_code
            ,e3_end_code
            ,e4_end_code
            ,e5_end_code
            ,e6_end_code
            ,transfer_total_span_days
            ,transfer_total_time_use
            ,main_total_span_days
            ,main_total_time_use
            ,end_center
            ,end_center_code
            ,start_center
            ,out_from_code
            ,out_collect_code
            ,out_to_regional_desc
            ,out_to_regional_code
            ,out_to_financial_center_desc
            ,out_to_financial_center_code
            ,out_to_provider_desc
            ,out_to_city_desc
            ,out_to_area_desc
            ,out_collect_name
            ,out_to_name
            ,out_from_name
            ,out_has_collect
            ,out_edge_planned_departure
            ,out_edge_planned_arrival_time
            ,out_edge2_planned_departure
            ,out_edge2_planned_arrival_time
            ,out_edge2_latest_warehouse_time
            ,out_edge_latest_warehouse_time
            ,out_edge_span_days_sign
            ,out_edge2_span_days_sign
            ,out_edge_deadline_sign_time
            ,out_edge2_deadline_sign_time
            ,branch_out_span_days
            ,branch_out_total_time
            ,effective_date
            ,expiration_date
            ,branch_in_center_stop_time
            ,center_branch_out_stop_time
            ,branch_in_center_span_days
            ,center_branch_out_span_days
            ,is_circuitous
            ,out_edge_span_days_arrive
            ,out_edge2_span_days_arrive
            ,in_nodes
            ,out_nodes
            ,total_days_use
            ,total_time_use
            ,total_days_t
            ,total_nodes
            ,working_days
            ,extra_in_collect_code
            ,extra_in_collect_name
            ,extra_in_line_name
            ,extra_in_latest_warehousing
            ,extra_in_planned_departure
            ,extra_in_planned_arrival
            ,extra_out_collect_code
            ,extra_out_collect_name
            ,extra_out_line_name
            ,extra_out_latest_warehousing
            ,extra_out_planned_departure
            ,extra_out_planned_arrival
            ,in_network_line_name
            ,in_collect_line_name
            ,out_network_line_name
            ,out_collect_line_name
            ,town_plus_time
            ,branch_in_ship_time
            ,branch_in_ship_span_days
            ,branch_out_ship_time
            ,branch_out_ship_span_days
            ,all_line_name
            ,out_town_plus_time
            ,in_edge_run_time
            ,in_edge_span_days
            ,in_edge2_run_time
            ,in_edge2_span_days
            ,in_edge3_run_time
            ,in_edge3_span_days
            ,out_edge_run_time
            ,out_edge_span_days
            ,out_edge2_run_time
            ,out_edge2_span_days
            ,out_edge3_run_time
            ,out_edge3_span_days
            ,e1_span_days
            ,e2_span_days
            ,e3_span_days
            ,e4_span_days
            ,e5_span_days
            ,e6_span_days
            ,delivery_time
            ,route_flow
            ,in_edge2_latest_warehousing
            ,e1_latest_warehousing
            ,e2_latest_warehousing
            ,e3_latest_warehousing
            ,e4_latest_arrival_time
            ,e5_latest_arrival_time
            ,e6_latest_arrival_time
            ,start_taking_shift
            ,end_send_shift
            ,warehouse_end_time
            ,reserve_1
            ,reserve_2
            ,route_contain_main_line
            ,direct_route_type
            ,in_line_day
            ,out_line_day
            ,in_ship_span_days
            ,in_manage_region_code
            ,in_manage_region_name
            ,out_manage_region_code
            ,out_manage_region_name
            ,latest_warehousing_time_quantum
            ,lasted_line_name
            ,platform_code_cn
            ,platform_cn
            ,effective_times_cn
            ,platform_code_zt
            ,platform_zt
            ,effective_times_zt
            ,platform_code_sf
            ,platform_sf
            ,effective_times_sf
            ,platform_code_yt
            ,platform_yt
            ,effective_times_yt
            ,platform_code_yd
            ,platform_yd
            ,effective_times_yd
            ,platform_code_thd
            ,platform_thd
            ,effective_times_thd
            ,platform_code_zjs
            ,platform_zjs
            ,effective_times_zjs
            ,if_cn_cp
            ,if_zt_cp
            ,if_sf_cp
            ,if_yt_cp
            ,if_yd_cp
            ,if_thd_cp
            ,if_zjs_cp
            ,if_cn_my
            ,if_zt_my
            ,if_sf_my
            ,if_yt_my
            ,if_yd_my
            ,if_thd_my
            ,if_zjs_my
            ,if_city
        )
        columns from path as (dt)
        set(
             in_from_code                          = in_from_code
            ,in_from_provider_code                 = in_from_provider_code
            ,in_from_city_code                     = in_from_city_code
            ,in_from_area_code                     = in_from_area_code
            ,out_to_provider_code                  = out_to_provider_code
            ,out_to_city_code                      = out_to_city_code
            ,out_to_area_code                      = out_to_area_code
            ,out_to_code                           = out_to_code
            ,in_branch_id                          = in_branch_id
            ,main_id                               = main_id
            ,out_branch_id                         = out_branch_id
            ,in_collect_code                       = in_collect_code
            ,in_to_code                            = in_to_code
            ,start_center_code                     = start_center_code
            ,search_type                           = search_type
            ,start_network_code                    = start_network_code
            ,in_from_regional_code                 = in_from_regional_code
            ,in_from_regional_desc                 = in_from_regional_desc
            ,in_from_financial_center_code         = in_from_financial_center_code
            ,in_from_financial_center_desc         = in_from_financial_center_desc
            ,in_from_provider_desc                 = in_from_provider_desc
            ,in_from_city_desc                     = in_from_city_desc
            ,in_from_area_desc                     = in_from_area_desc
            ,in_collect_name                       = in_collect_name
            ,in_from_name                          = in_from_name
            ,in_to_name                            = in_to_name
            ,in_edge_latest_warehousing            = in_edge_latest_warehousing
            ,in_edge_planned_departure             = in_edge_planned_departure
            ,in_edge_planned_arrival               = in_edge_planned_arrival
            ,in_edge2_planned_departure            = in_edge2_planned_departure
            ,in_edge2_planned_arrival              = in_edge2_planned_arrival
            ,branch_in_span_days                   = branch_in_span_days
            ,branch_in_total_time                  = branch_in_total_time
            ,is_main_route                         = is_main_route
            ,transfer_type                         = transfer_type
            ,num_of_transfer                       = num_of_transfer
            ,whole_route                           = whole_route
            ,e1_center_flow                        = e1_center_flow
            ,e1_line_code                          = e1_line_code
            ,e1_planned_departure                  = e1_planned_departure
            ,e1_runtime                            = e1_runtime
            ,e1_planned_arrival                    = e1_planned_arrival
            ,e1_e2_stop_time                       = e1_e2_stop_time
            ,e2_center_flow                        = e2_center_flow
            ,e2_line_code                          = e2_line_code
            ,e2_planned_departure                  = e2_planned_departure
            ,e2_runtime                            = e2_runtime
            ,e2_planned_arrival                    = e2_planned_arrival
            ,e2_e3_stop_time                       = e2_e3_stop_time
            ,e3_center_flow                        = e3_center_flow
            ,e3_line_code                          = e3_line_code
            ,e3_planned_departure                  = e3_planned_departure
            ,e3_runtime                            = e3_runtime
            ,e3_planned_arrival                    = e3_planned_arrival
            ,e3_e4_stop_time                       = e3_e4_stop_time
            ,e4_center_flow                        = e4_center_flow
            ,e4_line_code                          = e4_line_code
            ,e4_planned_departure                  = e4_planned_departure
            ,e4_runtime                            = e4_runtime
            ,e4_planned_arrival                    = e4_planned_arrival
            ,e4_e5_stop_time                       = e4_e5_stop_time
            ,e5_center_flow                        = e5_center_flow
            ,e5_line_code                          = e5_line_code
            ,e5_planned_departure                  = e5_planned_departure
            ,e5_runtime                            = e5_runtime
            ,e5_planned_arrival                    = e5_planned_arrival
            ,e5_e6_stop_time                       = e5_e6_stop_time
            ,e6_center_flow                        = e6_center_flow
            ,e6_line_code                          = e6_line_code
            ,e6_planned_departure                  = e6_planned_departure
            ,e6_runtime                            = e6_runtime
            ,e6_planned_arrival                    = e6_planned_arrival
            ,e6_e7_stop_time                       = e6_e7_stop_time
            ,e1_end_code                           = e1_end_code
            ,e2_end_code                           = e2_end_code
            ,e3_end_code                           = e3_end_code
            ,e4_end_code                           = e4_end_code
            ,e5_end_code                           = e5_end_code
            ,e6_end_code                           = e6_end_code
            ,transfer_total_span_days              = transfer_total_span_days
            ,transfer_total_time_use               = transfer_total_time_use
            ,main_total_span_days                  = main_total_span_days
            ,main_total_time_use                   = main_total_time_use
            ,end_center                            = end_center
            ,end_center_code                       = end_center_code
            ,start_center                          = start_center
            ,out_from_code                         = out_from_code
            ,out_collect_code                      = out_collect_code
            ,out_to_regional_desc                  = out_to_regional_desc
            ,out_to_regional_code                  = out_to_regional_code
            ,out_to_financial_center_desc          = out_to_financial_center_desc
            ,out_to_financial_center_code          = out_to_financial_center_code
            ,out_to_provider_desc                  = out_to_provider_desc
            ,out_to_city_desc                      = out_to_city_desc
            ,out_to_area_desc                      = out_to_area_desc
            ,out_collect_name                      = out_collect_name
            ,out_to_name                           = out_to_name
            ,out_from_name                         = out_from_name
            ,out_has_collect                       = out_has_collect
            ,out_edge_planned_departure            = out_edge_planned_departure
            ,out_edge_planned_arrival_time         = out_edge_planned_arrival_time
            ,out_edge2_planned_departure           = out_edge2_planned_departure
            ,out_edge2_planned_arrival_time        = out_edge2_planned_arrival_time
            ,out_edge2_latest_warehouse_time       = out_edge2_latest_warehouse_time
            ,out_edge_latest_warehouse_time        = out_edge_latest_warehouse_time
            ,out_edge_span_days_sign               = out_edge_span_days_sign
            ,out_edge2_span_days_sign              = out_edge2_span_days_sign
            ,out_edge_deadline_sign_time           = out_edge_deadline_sign_time
            ,out_edge2_deadline_sign_time          = out_edge2_deadline_sign_time
            ,branch_out_span_days                  = branch_out_span_days
            ,branch_out_total_time                 = branch_out_total_time
            ,effective_date                        = effective_date
            ,expiration_date                       = expiration_date
            ,branch_in_center_stop_time            = branch_in_center_stop_time
            ,center_branch_out_stop_time           = center_branch_out_stop_time
            ,branch_in_center_span_days            = branch_in_center_span_days
            ,center_branch_out_span_days           = center_branch_out_span_days
            ,is_circuitous                         = is_circuitous
            ,out_edge_span_days_arrive             = out_edge_span_days_arrive
            ,out_edge2_span_days_arrive            = out_edge2_span_days_arrive
            ,in_nodes                              = in_nodes
            ,out_nodes                             = out_nodes
            ,total_days_use                        = total_days_use
            ,total_time_use                        = total_time_use
            ,total_days_t                          = total_days_t
            ,total_nodes                           = total_nodes
            ,working_days                          = working_days
            ,extra_in_collect_code                 = extra_in_collect_code
            ,extra_in_collect_name                 = extra_in_collect_name
            ,extra_in_line_name                    = extra_in_line_name
            ,extra_in_latest_warehousing           = extra_in_latest_warehousing
            ,extra_in_planned_departure            = extra_in_planned_departure
            ,extra_in_planned_arrival              = extra_in_planned_arrival
            ,extra_out_collect_code                = extra_out_collect_code
            ,extra_out_collect_name                = extra_out_collect_name
            ,extra_out_line_name                   = extra_out_line_name
            ,extra_out_latest_warehousing          = extra_out_latest_warehousing
            ,extra_out_planned_departure           = extra_out_planned_departure
            ,extra_out_planned_arrival             = extra_out_planned_arrival
            ,in_network_line_name                  = in_network_line_name
            ,in_collect_line_name                  = in_collect_line_name
            ,out_network_line_name                 = out_network_line_name
            ,out_collect_line_name                 = out_collect_line_name
            ,town_plus_time                        = town_plus_time
            ,branch_in_ship_time                   = branch_in_ship_time
            ,branch_in_ship_span_days              = branch_in_ship_span_days
            ,branch_out_ship_time                  = branch_out_ship_time
            ,branch_out_ship_span_days             = branch_out_ship_span_days
            ,all_line_name                         = all_line_name
            ,out_town_plus_time                    = out_town_plus_time
            ,in_edge_run_time                      = in_edge_run_time
            ,in_edge_span_days                     = in_edge_span_days
            ,in_edge2_run_time                     = in_edge2_run_time
            ,in_edge2_span_days                    = in_edge2_span_days
            ,in_edge3_run_time                     = in_edge3_run_time
            ,in_edge3_span_days                    = in_edge3_span_days
            ,out_edge_run_time                     = out_edge_run_time
            ,out_edge_span_days                    = out_edge_span_days
            ,out_edge2_run_time                    = out_edge2_run_time
            ,out_edge2_span_days                   = out_edge2_span_days
            ,out_edge3_run_time                    = out_edge3_run_time
            ,out_edge3_span_days                   = out_edge3_span_days
            ,e1_span_days                          = e1_span_days
            ,e2_span_days                          = e2_span_days
            ,e3_span_days                          = e3_span_days
            ,e4_span_days                          = e4_span_days
            ,e5_span_days                          = e5_span_days
            ,e6_span_days                          = e6_span_days
            ,delivery_time                         = delivery_time
            ,route_flow                            = route_flow
            ,in_edge2_latest_warehousing           = in_edge2_latest_warehousing
            ,e1_latest_warehousing                 = e1_latest_warehousing
            ,e2_latest_warehousing                 = e2_latest_warehousing
            ,e3_latest_warehousing                 = e3_latest_warehousing
            ,e4_latest_arrival_time                = e4_latest_arrival_time
            ,e5_latest_arrival_time                = e5_latest_arrival_time
            ,e6_latest_arrival_time                = e6_latest_arrival_time
            ,start_taking_shift                    = start_taking_shift
            ,end_send_shift                        = end_send_shift
            ,warehouse_end_time                    = warehouse_end_time
            ,reserve_1                             = reserve_1
            ,reserve_2                             = reserve_2
            ,route_contain_main_line               = route_contain_main_line
            ,direct_route_type                     = direct_route_type
            ,in_line_day                           = in_line_day
            ,out_line_day                          = out_line_day
            ,in_ship_span_days                     = in_ship_span_days
            ,in_manage_region_code                 = in_manage_region_code
            ,in_manage_region_name                 = in_manage_region_name
            ,out_manage_region_code                = out_manage_region_code
            ,out_manage_region_name                = out_manage_region_name
            ,latest_warehousing_time_quantum       = latest_warehousing_time_quantum
            ,lasted_line_name                      = lasted_line_name
            ,platform_code_cn                      = platform_code_cn
            ,platform_cn                           = platform_cn
            ,effective_times_cn                    = effective_times_cn
            ,platform_code_zt                      = platform_code_zt
            ,platform_zt                           = platform_zt
            ,effective_times_zt                    = effective_times_zt
            ,platform_code_sf                      = platform_code_sf
            ,platform_sf                           = platform_sf
            ,effective_times_sf                    = effective_times_sf
            ,platform_code_yt                      = platform_code_yt
            ,platform_yt                           = platform_yt
            ,effective_times_yt                    = effective_times_yt
            ,platform_code_yd                      = platform_code_yd
            ,platform_yd                           = platform_yd
            ,effective_times_yd                    = effective_times_yd
            ,platform_code_thd                     = platform_code_thd
            ,platform_thd                          = platform_thd
            ,effective_times_thd                   = effective_times_thd
            ,platform_code_zjs                     = platform_code_zjs
            ,platform_zjs                          = platform_zjs
            ,effective_times_zjs                   = effective_times_zjs
            ,if_cn_cp                              = if_cn_cp
            ,if_zt_cp                              = if_zt_cp
            ,if_sf_cp                              = if_sf_cp
            ,if_yt_cp                              = if_yt_cp
            ,if_yd_cp                              = if_yd_cp
            ,if_thd_cp                             = if_thd_cp
            ,if_zjs_cp                             = if_zjs_cp
            ,if_cn_my                              = if_cn_my
            ,if_zt_my                              = if_zt_my
            ,if_sf_my                              = if_sf_my
            ,if_yt_my                              = if_yt_my
            ,if_yd_my                              = if_yd_my
            ,if_thd_my                             = if_thd_my
            ,if_zjs_my                             = if_zjs_my
            ,if_city                               = if_city
            ,dt                                    = dt
        )
    )
    WITH BROKER '{{{{ var.json.doris_brokers | random_choice }}}}'
    PROPERTIES ('timeout'='{timeout}', 'max_filter_ratio'='0.0')""",
    poke_sql=f"SHOW LOAD FROM jms_dm WHERE label = '{label}' ORDER BY CreateTime DESC LIMIT 1",
    sql_on_kill=f"CANCEL LOAD FROM jms_dm WHERE LABEL = '{label}'",
    success=lambda r: r[2] == 'FINISHED',
    failure=lambda r: (r[2] is not None and r[2] == 'CANCELLED', str(r[7])),
    poke_interval=60,
    execution_timeout=timedelta(seconds=timeout + 120), )

# doris_jms_dm__dm_route_whole_shift_base_dt << jms_dm__dm_route_whole_shift_base_dt
